<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Scholarship_Model extends CI_Model {
	
	public function get_scholarship_history($username) {
		$query = "SELECT ss.name, ssp.name AS sponsor, ss.period_start, ss. period_end, ssa.status 
			FROM smb_scholarships AS ss JOIN smb_scholarship_applicants AS ssa ON ss.id = ssa.id
			JOIN smb_sponsors AS ssp ON ssp.id = ss.sponsor_id 
			WHERE username = ? AND (status = 'aktif' OR status = 'non-aktif') ORDER BY status, period_end DESC";
		$result = $this->db->query($query, array($username));
		if ($result->num_rows() > 0) {
			return $result->result();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function get_register_history($username) {
		$query = "SELECT ss.name, ssp.name AS sponsor, ss.period_start, ss. period_end, ssa.status 
			FROM smb_scholarships AS ss JOIN smb_scholarship_applicants AS ssa ON ss.id = ssa.id
			JOIN smb_sponsors AS ssp ON ssp.id = ss.sponsor_id 
			WHERE username = ? AND status IN ('mendaftar', 'ditolak', 'tidak terverifikasi', 
			'terverifikasi') ORDER BY status, period_end DESC";
		$result = $this->db->query($query, array($username));
		if ($result->num_rows() > 0) {
			return $result->result();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function get_scholarship_history_for_sponsor($username, $sponsor) {
		$query = "SELECT ss.name, ssp.name AS sponsor, ss.period_start, ss. period_end, ssa.status 
			FROM smb_scholarships AS ss JOIN smb_scholarship_applicants AS ssa ON ss.id = ssa.id
			JOIN smb_sponsors AS ssp ON ssp.id = ss.sponsor_id 
			JOIN smb_sponsor_users AS ssu ON ssu.id = ssp.id
			WHERE ssa.username = ? AND (status = 'aktif' OR status = 'non-aktif') 
			AND ssu.username = ? ORDER BY status, period_end DESC";
		$result = $this->db->query($query, array($username, $sponsor));
		if ($result->num_rows() > 0) {
			return $result->result();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function get_register_history_for_sponsor($username, $sponsor) {
		$query = "SELECT ss.name, ssp.name AS sponsor, ss.period_start, ss. period_end, ssa.status 
			FROM smb_scholarships AS ss JOIN smb_scholarship_applicants AS ssa ON ss.id = ssa.id
			JOIN smb_sponsors AS ssp ON ssp.id = ss.sponsor_id 
			JOIN smb_sponsor_users AS ssu ON ssu.id = ssp.id
			WHERE ssa.username = ? AND status IN ('mendaftar', 'ditolak', 'tidak terverifikasi', 'terverifikasi') 
			AND ssu.username = ? ORDER BY status, period_end DESC";
		$result = $this->db->query($query, array($username, $sponsor));
		if ($result->num_rows() > 0) {
			return $result->result();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}

	public function list_scholarship_by_sponsor($sponsor) {
		$query = "SELECT ss.id, ss.name FROM smb_scholarships AS ss JOIN smb_sponsor_users AS ssu
			ON ss.sponsor_id = ssu.id WHERE ssu.username = ?";
		$result = $this->db->query($query, array($sponsor));
		if ($result->num_rows() > 0) {
			return $result->result();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function list_offered_scholarship($offset, $limit, $type, $name, $sponsor) {
		switch ($type) {
			case PENDAFTARAN:
				$type = " AND CURRENT_DATE() <= deadline ";
				break;
			case SELEKSI:
				$type = " AND deadline < CURRENT_DATE() AND CURRENT_DATE() <= period_start ";
				break;
			case AKTIF:
				$type = " AND period_start < CURRENT_DATE() AND CURRENT_DATE() <= period_end ";
				break;
			case NONAKTIF:
				$type = " AND period_end < CURRENT_DATE() ";
				break;
		}
		if ($name == '') {
			$name = '%';
		} else {
			$name = '%'.$name.'%';
		}
		if ($sponsor == '') {
			$sponsor = '%';
		}
		$query = "SELECT ss.* FROM smb_scholarships AS ss JOIN smb_sponsors AS ssp
			ON ss.sponsor_id = ssp.id WHERE ss.name LIKE ? AND ssp.id LIKE ? ".
			$type." ORDER BY ss.deadline DESC, ss.period_end DESC, ss.period_start DESC LIMIT ?, ?";
		$result = $this->db->query($query, array($name, $sponsor, intval($offset), intval($limit)));
		if ($result->num_rows() > 0) {
			return $result->result();
		} else {
			return NULL;
		}
	}
	
	public function count_all_scholarship($type, $name, $sponsor) {
		switch ($type) {
			case PENDAFTARAN:
				$type = " AND CURRENT_DATE() <= deadline ";
				break;
			case SELEKSI:
				$type = " AND deadline < CURRENT_DATE() AND CURRENT_DATE() <= period_start ";
				break;
			case AKTIF:
				$type = " AND period_start < CURRENT_DATE() AND CURRENT_DATE() <= period_end ";
				break;
			case NONAKTIF:
				$type = " AND period_end < CURRENT_DATE() ";
				break;
		}
		if ($name == '') {
			$name = '%';
		} else {
			$name = '%'.$name.'%';
		}
		if ($sponsor == '') {
			$sponsor = '%';
		}
		$query = "SELECT COUNT(DISTINCT ss.id) AS sum FROM smb_scholarships AS ss JOIN smb_sponsors AS ssp
			ON ss.sponsor_id = ssp.id WHERE ss.name LIKE ? AND ssp.id LIKE ? ".$type;
		$result = $this->db->query($query, array($name, $sponsor));
		if ($result->num_rows() == 1) {
			return $result->row()->sum;
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function get_offered_scholarship($username, $offset, $limit) {
		$query = "SELECT * FROM ((SELECT ss.id, ss.name, ss.period_start, ss.period_end, ss.deadline, ss.details, ssa.status
			FROM smb_scholarships AS ss LEFT JOIN smb_scholarship_applicants AS ssa 
			ON ss.id = ssa.id  AND ssa.username = ? WHERE type = 'publik' AND (ssa.status NOT IN ('aktif', 'non-aktif') OR ssa.status IS NULL))
			UNION ALL (SELECT ss.id, ss.name, ss.period_start, ss.period_end, ss.deadline, ss.details, ssa.status
			FROM smb_scholarships AS ss JOIN smb_scholarship_applicants AS ssa 
			ON ss.id = ssa.id WHERE ssa.username = ? AND ss.type = 'privat' AND (ssa.status NOT IN ('aktif', 'non-aktif') OR ssa.status IS NULL))) 
			AS tmp ORDER BY deadline DESC, name LIMIT ?, ?";
		$result = $this->db->query($query, array($username, $username, intval($offset), intval($limit)));
		if ($result->num_rows() > 0) {
			return $result->result();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function count_offered_scholarship($username) {
		$query = "SELECT COUNT(*) AS sum FROM ((SELECT ss.id, ss.name, ss.period_start, ss.period_end, ss.deadline, ss.details, ssa.status
			FROM smb_scholarships AS ss LEFT JOIN smb_scholarship_applicants AS ssa 
			ON ss.id = ssa.id AND ssa.username = ? WHERE type = 'publik' AND (ssa.status NOT IN ('aktif', 'non-aktif') OR ssa.status IS NULL))
			UNION ALL (SELECT ss.id, ss.name, ss.period_start, ss.period_end, ss.deadline, ss.details, ssa.status
			FROM smb_scholarships AS ss JOIN smb_scholarship_applicants AS ssa 
			ON ss.id = ssa.id WHERE ssa.username = ? AND ss.type = 'privat' AND (ssa.status NOT IN ('aktif', 'non-aktif') OR ssa.status IS NULL))) AS tmp";
		$result = $this->db->query($query, array($username, $username));
		if ($result->num_rows() == 1) {
			return $result->row()->sum;
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function get_obtained_scholarship($username) {
		$query = "SELECT ss.id, ss.name, ss.period_start, ss.period_end, ssa.status, ssa.delivery
			FROM smb_scholarships AS ss JOIN smb_scholarship_applicants AS ssa
			ON ss.id = ssa.id WHERE ssa.username = ? AND ssa.status IN ('aktif', 'non-aktif')
			ORDER BY ssa.status, ss.period_end";
		$result = $this->db->query($query, array($username));
		if ($result->num_rows() > 0) {
			return $result->result();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function update_reception_status($statuses, $username) {
		$query = "UPDATE smb_scholarship_applicants SET delivery = ? WHERE id = ? AND username = ?";
		
		$this->db->trans_start();
		foreach ($statuses as $key => $status) {
			$this->db->query($query, array($status, $key, $username));
		}
		$this->db->trans_complete();
		
		if ($this->db->trans_status() === FALSE) {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return FALSE;
		} else {
			return TRUE;
		}
	}

	public function update_reception_scholarship($id, $delivery) {
		$query = "SELECT * FROM smb_scholarships WHERE period_start <= CURRENT_DATE AND CURRENT_DATE <= period_end AND id = ?";
		$result = $this->db->query($query, array($id));
		if ($result->num_rows() == 1) {
			$query = "UPDATE smb_scholarship_applicants SET delivery = ? WHERE id = ?";
			$this->db->trans_start();
			$this->db->query($query, array($delivery, $id));
			$this->db->trans_complete();
			if ($this->db->trans_status() === FALSE) {
				$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
				return FALSE;
			} else {
				return TRUE;
			}
		} else {
			return FALSE;
		}
	}
	
	public function get_scholarship_by_id_and_username($id, $username) {
		$query = "SELECT ss.* FROM smb_scholarships AS ss LEFT JOIN smb_scholarship_applicants AS ssa
			ON ss.id = ssa.id AND (ssa.username IS NULL OR ssa.username = ?) WHERE ss.id = ? AND ((ss.type = 'publik' AND ssa.status IS NULL) 
			OR (ss.type = 'privat' AND ssa.status = 'ditawarkan' AND ssa.username = ?)) AND ss.deadline >= CURDATE()";
		$result = $this->db->query($query, array($username, $id, $username));
		if ($result->num_rows() == 1) {
			return $result->row();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function get_scholarship_by_id_username_and_status($id, $username) {
		$query = "SELECT ss.*, ssa.status FROM smb_scholarships AS ss JOIN smb_scholarship_applicants AS ssa ON ss.id = ssa.id 
			AND ss.id = ? AND ssa.username = ? WHERE ssa.status IN ('terverifikasi', 'ditolak', 'mendaftar', 'tidak terverifikasi')";
		$result = $this->db->query($query, array($id, $username));
		if ($result->num_rows() == 1) {
			return $result->row();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function get_required_file($id) {
		$query = "SELECT sfc.name, sfc.id FROM smb_scholarships AS ss JOIN smb_required_files AS srf ON ss.id = srf.id 
			AND ss.id = ? JOIN smb_file_categories as sfc ON srf.file_id = sfc.id";
		$result = $this->db->query($query, array($id));
		if ($result->num_rows() > 0) {
			return $result->result();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function get_provided_scholarship($sponsor, $offset, $limit, $type) {
		switch ($type) {
			case PENDAFTARAN:
				$type = " WHERE CURRENT_DATE() <= ss.deadline ";
				break;
			case SELEKSI:
				$type = " WHERE ss.deadline < CURRENT_DATE() AND CURRENT_DATE() <= ss.period_start ";
				break;
			case AKTIF:
				$type = " WHERE ss.period_start < CURRENT_DATE() AND CURRENT_DATE() <= ss.period_end ";
				break;
			case NONAKTIF:
				$type = " WHERE ss.period_end < CURRENT_DATE() ";
				break;
		}
		$query = "SELECT DISTINCT ss.*, (SELECT GROUP_CONCAT(DISTINCT delivery SEPARATOR ', ') FROM smb_scholarship_applicants WHERE id = ss.id) AS delivery
			FROM smb_scholarships AS ss JOIN smb_sponsor_users AS ssu 
			ON ss.sponsor_id = ssu.id AND ssu.username = ? ".$type."ORDER BY ss.deadline DESC, ss.period_end DESC, ss.period_start DESC LIMIT ?, ?";
		$result = $this->db->query($query, array($sponsor, intval($offset), intval($limit)));
		if ($result->num_rows() > 0) {
			return $result->result();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function count_provided_scholarship($sponsor, $type) {
		switch ($type) {
			case PENDAFTARAN:
				$type = " WHERE CURRENT_DATE() <= ss.deadline ";
				break;
			case SELEKSI:
				$type = " WHERE ss.deadline < CURRENT_DATE() AND CURRENT_DATE() <= ss.period_start ";
				break;
			case AKTIF:
				$type = " WHERE ss.period_start < CURRENT_DATE() AND CURRENT_DATE() <= ss.period_end ";
				break;
			case NONAKTIF:
				$type = " WHERE ss.period_end < CURRENT_DATE() ";
				break;
		}
		$query = "SELECT COUNT(DISTINCT ss.id) AS sum FROM smb_scholarships AS ss JOIN smb_sponsor_users AS ssu
			ON ss.sponsor_id = ssu.id AND ssu.username = ?".$type;
		$result = $this->db->query($query, array($sponsor));
		if ($result->num_rows() == 1) {
			return $result->row()->sum;
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function get_scholarship_by_id_and_status($id, $status) {
		switch ($status) {
			case PENDAFTARAN:
				$status = " WHERE CURRENT_DATE() <= ss.deadline ";
				break;
			case SELEKSI:
				$status = " WHERE ss.deadline < CURRENT_DATE() AND CURRENT_DATE() <= ss.period_start ";
				break;
			case AKTIF:
				$status = " WHERE ss.period_start < CURRENT_DATE() AND CURRENT_DATE() <= ss.period_end ";
				break;
			case NONAKTIF:
				$status = " WHERE ss.period_end < CURRENT_DATE() ";
				break;
		}
		$query = "
			SELECT ss.*, ssp.name AS sponsor,
				(IFNULL((SELECT GROUP_CONCAT(sfc.name SEPARATOR ', ')
				FROM smb_file_categories AS sfc JOIN smb_required_files AS srf
				ON sfc.id = srf.file_id AND srf.id = ?), '-')) as required_files
			FROM smb_scholarships AS ss JOIN smb_sponsors AS ssp
				ON ss.sponsor_id = ssp.id AND ss.id = ?
			".$status;
		$result = $this->db->query($query, array($id, $id));
		if ($result->num_rows() == 1) {
			return $result->row();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function get_scholarship_by_id_and_sponsor($id, $sponsor) {
		$query = "SELECT ss.*
			FROM smb_scholarships AS ss JOIN smb_sponsor_users AS ssu
			ON ss.sponsor_id = ssu.id AND ssu.username = ? WHERE ss.id = ?";
		$result = $this->db->query($query, array($sponsor, $id));
		if ($result->num_rows() == 1) {
			return $result->row();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function get_reciever_scholarship($id, $offset, $limit, $type, $sortby = '', $method = '') {
		switch ($type) {
			case PENDAFTARAN:
				$type = " AND ssa.status = 'mendaftar'";
				break;
			case SELEKSI:
				$type = " AND ssa.status IN ('mendaftar', 'terverifikasi', 'ditolak', 'direkomendasikan', 'aktif', 'tidak terverifikasi')";
				break;
			case AKTIF:
				$type = " AND ssa.status = 'aktif'";
				break;
			case NONAKTIF:
				$type = " AND ssa.status IN ('non-aktif', 'aktif')";
				break;
			case TERVERIFIKASI:
				$type = " AND ssa.status = 'terverifikasi'";
				break;
			case PRIVAT:
				$type = " AND ssa.status = 'ditawarkan'";
				break;
			case DOCUMENT_CHECK:
				$type = " AND ssa.status IN ('terverifikasi', 'mendaftar')";
				break;
		}
		switch ($sortby) {
			case 'name':
				$sortby = " ORDER BY ss.name ";
				break;
			case 'year':
				$sortby = " ORDER BY ss.year ";
				break;
			case 'major':
				$sortby = " ORDER BY ss.major ";
				break;
			default:
				$sortby = " ORDER BY ss.year, sm.major, ss.name ";
				break; 
		}
		switch ($method) {
			case 'ascending':
				$method = " ASC ";
				break;
			case 'descending':
				$method = " DESC ";
				break;
			default:
				$method = "";
				break;
		}
		$query = "SELECT ss.id, ss.name, sm.major, ss.year, ssa.status, ssa.delivery FROM smb_scholarship_applicants AS ssa JOIN smb_students AS ss
			ON ssa.username = ss.username AND ssa.id = ? ".$type." 
			JOIN smb_majors AS sm ON sm.id = ss.major_id ".$sortby.$method."LIMIT ?, ?";
		$result = $this->db->query($query, array($id, intval($offset), intval($limit)));
		if ($result->num_rows() > 0) {
			return $result->result();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function apply_scholarship($id, $username) {
		$query = "INSERT INTO smb_scholarship_applicants (id, username, status, delivery) 
			VALUES (?, ?, 'mendaftar', 'none') ON DUPLICATE KEY UPDATE status = 'mendaftar'";
		
		$this->db->trans_start();
		$this->db->query($query, array($id, $username));
		$this->db->trans_complete();
		
		if ($this->db->trans_status() === FALSE) {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return FALSE;
		} else {
			return TRUE;
		}
	}
	
	public function get_document_by_scholarid_id_and_username($scholarid, $id, $username) {
		$query = "SELECT ssf.id, sfc.name, ssf.upload_time, ssf.details FROM smb_required_files AS srf JOIN smb_student_files AS ssf ON 
			srf.id = ? AND srf.file_id = ? AND ssf.username = ?
			JOIN smb_file_categories AS sfc ON sfc.id = srf.file_id";
		$result = $this->db->query($query, array($scholarid, $id, $username));
		if ($result->num_rows() == 1) {
			return $result->row();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function get_sponsor() {
		$query = "SELECT * FROM smb_sponsors";
		$result = $this->db->query($query);
		if ($result->num_rows() > 0) {
			return $result->result();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function add_scholarship($scholar) {
		$addfiles = $scholar['addfiles'];
		$filequery = "";
		$fileinquery = "";
		if ($addfiles != FALSE) {
			$addfiles = array_map('trim', $addfiles);
			$addfiles = array_filter($addfiles);
			if (count($addfiles) != 0) {
				foreach ($addfiles as $key => $file) {
					$filequery = $filequery.'(UUID(), ?), ';
					$fileinquery = $fileinquery.'?, ';
				}
			}
		}
		$fileids = $scholar['requiredfiles'];
		if ($fileids == FALSE) {
			$fileids = array();
		}
		
		$query = "INSERT INTO smb_scholarships (id, name, period_start, period_end, deadline,
			type, sponsor_id, details) VALUES(UUID(), ?, STR_TO_DATE(?, '%Y-%m-%d'), STR_TO_DATE(?, '%Y-%m-%d'), STR_TO_DATE(?, '%Y-%m-%d'), ?, ?, ?)";
		$selectquery = "SELECT id FROM smb_scholarships WHERE sponsor_id = ? AND name = ? AND period_start = STR_TO_DATE(?, '%Y-%m-%d')
			AND period_end = STR_TO_DATE(?, '%Y-%m-%d') ";
		
		$this->db->trans_start();
		if ($filequery != '') {
			$filequery = substr($filequery, 0, -2);
			$fileinquery = substr($fileinquery, 0, -2);
			$addfilesquery = "INSERT INTO smb_file_categories (id, name) VALUES ".$filequery;
			$this->db->query($addfilesquery, $addfiles);
			$getfilesquery = "SELECT id FROM smb_file_categories WHERE name IN (".$fileinquery.")";
			$newfileids = $this->db->query($getfilesquery, $addfiles);
			$newfileids = $newfileids->result();
			foreach ($newfileids as $newfileid) {
				$fileids[] = $newfileid->id;
			}
		}
		$this->db->query($query, array($scholar['name'], $scholar['periodstart'], $scholar['periodend']
				, $scholar['deadline'], $scholar['type'], $scholar['sponsor_id'], $scholar['details']));
		$id = $this->db->query($selectquery, array($scholar['sponsor_id'], $scholar['name'], $scholar['periodstart'], $scholar['periodend']));
		$id = $id->row()->id;
		$ids = "";
		foreach ($fileids as $key => $fileid) {
			$ids = $ids."('".$id."', ?), ";
		}
		if ($ids != "") {
			$ids = substr($ids, 0, -2);
			$requiredquery = "INSERT INTO smb_required_files (id, file_id) VALUES ".$ids;
			$this->db->query($requiredquery, $fileids);
		}
		$this->db->trans_complete();
		
		if ($this->db->trans_status() === FALSE) {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return FALSE;
		} else {
			return TRUE;
		}
	}
	
	public function edit_scholarship($id, $scholar) {
		$addfiles = $scholar['addfiles'];
		$filequery = "";
		$fileinquery = "";
		if ($addfiles != FALSE) {
			$addfiles = array_map('trim', $addfiles);
			$addfiles = array_filter($addfiles);
			if (count($addfiles) != 0) {
				foreach ($addfiles as $key => $file) {
					$filequery = $filequery.'(UUID(), ?), ';
					$fileinquery = $fileinquery.'?, ';
				}
			}
		}
		$fileids = $scholar['requiredfiles'];
		if ($fileids == FALSE) {
			$fileids = array();
		}
		
		$query = "UPDATE smb_scholarships SET name = ?, period_start = STR_TO_DATE(?, '%Y-%m-%d'), 
			period_end = STR_TO_DATE(?, '%Y-%m-%d'), deadline = STR_TO_DATE(?, '%Y-%m-%d'),
			type = ?, sponsor_id = ?, details = ? WHERE id = ?";
		$deletequery = "DELETE FROM smb_required_files WHERE id = ?";
		
		$this->db->trans_start();
		$this->db->query($deletequery, array($id));
		if ($filequery != '') {
			$filequery = substr($filequery, 0, -2);
			$fileinquery = substr($fileinquery, 0, -2);
			$addfilesquery = "INSERT INTO smb_file_categories (id, name) VALUES ".$filequery;
			$this->db->query($addfilesquery, $addfiles);
			$getfilesquery = "SELECT id FROM smb_file_categories WHERE name IN (".$fileinquery.")";
			$newfileids = $this->db->query($getfilesquery, $addfiles);
			$newfileids = $newfileids->result();
			foreach ($newfileids as $newfileid) {
				$fileids[] = $newfileid->id;
			}
		}
		if ($scholar['type'] == 'publik') {
			$deleteinvited = "DELETE FROM smb_scholarship_applicants WHERE id = ? AND status = 'ditawarkan'";
			$this->db->query($deleteinvited, array($id));
		}
		$this->db->query($query, array($scholar['name'], $scholar['periodstart'], $scholar['periodend']
				, $scholar['deadline'], $scholar['type'], $scholar['sponsor_id'], $scholar['details'], $id));
		$ids = "";
		foreach ($fileids as $key => $fileid) {
			$ids = $ids."('".$id."', ?), ";
		}
		if ($ids != "") {
			$ids = substr($ids, 0, -2);
			$requiredquery = "INSERT INTO smb_required_files (id, file_id) VALUES ".$ids;
			$this->db->query($requiredquery, $fileids);
		}
		$this->db->trans_complete();
		
		if ($this->db->trans_status() === FALSE) {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return FALSE;
		} else {
			return TRUE;
		}
	}
	
	public function get_scholarship_id($sponsorid, $name, $periodstart, $periodend) {
		$query = "SELECT id FROM smb_scholarships WHERE sponsor_id = ? AND name = ? AND period_start = STR_TO_DATE(?, '%Y-%m-%d')
			AND period_end = STR_TO_DATE(?, '%Y-%m-%d') ";
		$result = $this->db->query($query, array($sponsorid, $name, $periodstart, $periodend));
		if ($result->num_rows() == 1) {
			return $result->row()->id;
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function count_reciever_scholarship($id, $type) {
		switch ($type) {
			case PENDAFTARAN:
				$type = " AND status = 'mendaftar'";
				break;
			case SELEKSI:
				$type = " AND status IN ('mendaftar', 'terverifikasi', 'ditolak', 'direkomendasikan', 'aktif', 'tidak terverifikasi')";
				break;
			case AKTIF:
				$type = " AND status = 'aktif'";
				break;
			case NONAKTIF:
				$type = " AND status IN ('non-aktif', 'aktif')";
				break;
			case TERVERIFIKASI:
				$type = " AND status = 'terverifikasi'";
				break;
			case PRIVAT:
				$type = " AND status = 'ditawarkan'";
				break;
			case DOCUMENT_CHECK:
				$type = " AND status IN ('terverifikasi', 'mendaftar')";
				break;
		}
		$query = "SELECT COUNT(*) AS sum FROM smb_scholarship_applicants WHERE id = ? ".$type;
		$result = $this->db->query($query, array($id));
		if ($result->num_rows() == 1) {
			return $result->row()->sum;
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function get_selection_counter($id) {
		$query = "SELECT COUNT(*) AS sum FROM smb_scholarship_applicants WHERE id = ? AND
			status = 'ditolak'";
		$result2 = $this->db->query($query, array($id));
		$query = "SELECT COUNT(*) AS sum FROM smb_scholarship_applicants WHERE id = ? AND
			status IN ('mendaftar', 'direkomendasikan')";
		$result1 = $this->db->query($query, array($id));
		$query = "SELECT COUNT(*) AS sum FROM smb_scholarship_applicants WHERE id = ? AND
			status IN ('aktif', 'non-aktif')";
		$result3 = $this->db->query($query, array($id));
		if ($result1->num_rows() == 1 && $result2->num_rows() == 1) {
			return array('mendaftar' => $result1->row()->sum, 'ditolak' => $result2->row()->sum, 'diterima' => $result3->row()->sum);
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function get_delivery_status($id) {
		$query = "SELECT COUNT(DISTINCT id) AS sum FROM smb_scholarship_applicants WHERE delivery IN ('diterima', 'sudah dikirim') AND id = ?";
		$result = $this->db->query($query, array($id));
		if ($result->num_rows() == 1) {
			if (intval($result->row()->sum) == 1) {
				return 'sudah dikirim';
			} else {
				return 'belum dikirim';
			}
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function count_verify_student($id, $name, $year, $major) {
		$name = '%'.$name.'%';
		if ($year == '') {
			$year = '%';
		}
		if ($major == '') {
			$major = '%';
		}
		$query = "SELECT COUNT(*) AS sum FROM smb_students ss JOIN smb_majors sm ON ss.major_id = sm.id
			LEFT JOIN smb_scholarship_applicants AS ssa ON ssa.username = ss.username AND ssa.id = ?  
			WHERE ss.name LIKE ? AND ss.year LIKE ? AND ss.major_id LIKE ? 
			AND ssa.status IN ('terverifikasi', 'mendaftar')"; 
		$result = $this->db->query($query, array($id, $name, $year, $major));
		if ($result->num_rows() == 1) {
			return $result->row()->sum;
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function count_invite_student($id, $name, $year, $major, $gpa) {
		$name = '%'.$name.'%';
		if ($year == '') {
			$year = '%';
		}
		if ($major == '') {
			$major = '%';
		}
		$query = "SELECT COUNT(*) AS sum FROM smb_students ss JOIN smb_majors sm ON ss.major_id = sm.id
			LEFT JOIN smb_scholarship_applicants AS ssa ON ssa.username = ss.username AND ssa.id = ?  
			WHERE ss.name LIKE ? AND ss.year LIKE ? AND ss.major_id LIKE ? AND ss.gpa >= ? 
			AND (ssa.status IS NULL OR ssa.status = 'ditawarkan')";
		$result = $this->db->query($query, array($id, $name, $year, $major, $gpa));
		if ($result->num_rows() == 1) {
			return $result->row()->sum;
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function count_recomend_student($id, $name, $year, $major, $gpa) {
		$name = '%'.$name.'%';
		if ($year == '') {
			$year = '%';
		}
		if ($major == '') {
			$major = '%';
		}
		$query = "SELECT COUNT(*) AS sum FROM smb_students ss JOIN smb_majors sm ON ss.major_id = sm.id
			LEFT JOIN smb_scholarship_applicants AS ssa ON ssa.username = ss.username AND ssa.id = ?  
			WHERE ss.name LIKE ? AND ss.year LIKE ? AND ss.major_id LIKE ? AND ss.gpa >= ? 
			AND (ssa.status IN ('terverifikasi', 'direkomendasikan'))";
		$result = $this->db->query($query, array($id, $name, $year, $major, $gpa));
		if ($result->num_rows() == 1) {
			return $result->row()->sum;
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function count_selection_student($id, $name, $year, $major, $gpa) {
		$name = '%'.$name.'%';
		if ($year == '') {
			$year = '%';
		}
		if ($major == '') {
			$major = '%';
		}
		$query = "SELECT COUNT(*) AS sum FROM smb_students ss JOIN smb_majors sm ON ss.major_id = sm.id
			LEFT JOIN smb_scholarship_applicants AS ssa ON ssa.username = ss.username AND ssa.id = ?  
			WHERE ss.name LIKE ? AND ss.year LIKE ? AND ss.major_id LIKE ? AND ss.gpa >= ? 
			AND (ssa.status IN ('terverifikasi', 'direkomendasikan', 'aktif', 'non-aktif', 'ditolak'))";
		$result = $this->db->query($query, array($id, $name, $year, $major, $gpa));
		if ($result->num_rows() == 1) {
			return $result->row()->sum;
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function search_verify_student($id, $offset, $limit, $name, $year, $major) {
		$name = '%'.$name.'%';
		if ($year == '') {
			$year = '%';
		}
		if ($major == '') {
			$major = '%';
		}
		$query = "SELECT ss.*, sm.major, ssa.status FROM smb_students ss JOIN smb_majors sm ON ss.major_id = sm.id
			LEFT JOIN smb_scholarship_applicants AS ssa ON ssa.username = ss.username AND ssa.id = ?  
			WHERE ss.name LIKE ? AND ss.year LIKE ? AND ss.major_id LIKE ?  
			AND ssa.status IN ('mendaftar', 'terverifikasi') LIMIT ?, ?";
		$result = $this->db->query($query, array($id, $name, $year, $major, intval($offset), intval($limit)));
		if ($result->num_rows() > 0) {
			return $result->result();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function search_invite_student($id, $offset, $limit, $name, $year, $major, $gpa) {
		$name = '%'.$name.'%';
		if ($year == '') {
			$year = '%';
		}
		if ($major == '') {
			$major = '%';
		}
		$query = "SELECT ss.*, sm.major, ssa.status FROM smb_students ss JOIN smb_majors sm ON ss.major_id = sm.id
			LEFT JOIN smb_scholarship_applicants AS ssa ON ssa.username = ss.username AND ssa.id = ?  
			WHERE ss.name LIKE ? AND ss.year LIKE ? AND ss.major_id LIKE ? AND ss.gpa >= ? 
			AND (ssa.status IS NULL OR ssa.status = 'ditawarkan') LIMIT ?, ?";
		$result = $this->db->query($query, array($id, $name, $year, $major, $gpa, intval($offset), intval($limit)));
		if ($result->num_rows() > 0) {
			return $result->result();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function search_recomend_student($id, $offset, $limit, $name, $year, $major, $gpa) {
		$name = '%'.$name.'%';
		if ($year == '') {
			$year = '%';
		}
		if ($major == '') {
			$major = '%';
		}
		$query = "SELECT ss.*, sm.major, ssa.status FROM smb_students ss JOIN smb_majors sm ON ss.major_id = sm.id
			LEFT JOIN smb_scholarship_applicants AS ssa ON ssa.username = ss.username AND ssa.id = ?  
			WHERE ss.name LIKE ? AND ss.year LIKE ? AND ss.major_id LIKE ? AND ss.gpa >= ? 
			AND (ssa.status IN ('terverifikasi', 'direkomendasikan')) LIMIT ?, ?";
		$result = $this->db->query($query, array($id, $name, $year, $major, $gpa, intval($offset), intval($limit)));
		if ($result->num_rows() > 0) {
			return $result->result();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function search_selection_student($id, $offset, $limit, $name, $year, $major, $gpa) {
		$name = '%'.$name.'%';
		if ($year == '') {
			$year = '%';
		}
		if ($major == '') {
			$major = '%';
		}
		$query = "SELECT ss.*, sm.major, ssa.status FROM smb_students ss JOIN smb_majors sm ON ss.major_id = sm.id
			LEFT JOIN smb_scholarship_applicants AS ssa ON ssa.username = ss.username AND ssa.id = ?  
			WHERE ss.name LIKE ? AND ss.year LIKE ? AND ss.major_id LIKE ? AND ss.gpa >= ? 
			AND (ssa.status IN ('terverifikasi', 'direkomendasikan', 'aktif', 'non-aktif', 'ditolak')) LIMIT ?, ?";
		$result = $this->db->query($query, array($id, $name, $year, $major, $gpa, intval($offset), intval($limit)));
		if ($result->num_rows() > 0) {
			return $result->result();
		} else {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return NULL;
		}
	}
	
	public function update_status_applicant($id, $username, $status, $delete) {
		if ($delete == FALSE) {
			$query = "INSERT INTO smb_scholarship_applicants (id, username, status, delivery) 
				VALUES (?, ?, ?, 'none') ON DUPLICATE KEY UPDATE status = ?";
		} else {
			$query = "DELETE FROM smb_scholarship_applicants WHERE id = ? AND username = ?";
		}
		
		$this->db->trans_start();
		if ($delete == FALSE) {
			$this->db->query($query, array($id, $username, $status, $status));
		} else {
			$this->db->query($query, array($id, $username));
		}
		$this->db->trans_complete();
		
		if ($this->db->trans_status() === FALSE) {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return FALSE;
		} else {
			return TRUE;
		}
	}
	
	public function approve_applicant($id, $username, $status) {
		if ($status == 'false') {
			$status = 'terverifikasi';
		} else if ($status == 'true') {
			$status = 'aktif';
		}
		$query = "UPDATE smb_scholarship_applicants SET status = ? WHERE id = ? AND username = ?";
		
		$this->db->trans_start();
		$this->db->query($query, array($status, $id, $username));
		$this->db->trans_complete();
		
		if ($this->db->trans_status() === FALSE) {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return FALSE;
		} else {
			return TRUE;
		}
	}
	
	public function reject_registrant($id) {
		$query = "UPDATE smb_scholarship_applicants SET status = 'tidak terverifikasi' WHERE status IN ('mendaftar') AND id = ?";
		
		$this->db->trans_start();
		$this->db->query($query, array($id));
		$this->db->trans_complete();
		
		if ($this->db->trans_status() === FALSE) {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return FALSE;
		} else {
			return TRUE;
		}
	}
	
	public function reject_applicant($id) {
		$query = "UPDATE smb_scholarship_applicants SET status = 'ditolak' WHERE status IN ('terverifikasi', 
			'direkomendasikan') AND id = ?";
		
		$this->db->trans_start();
		$this->db->query($query, array($id));
		$this->db->trans_complete();
		
		if ($this->db->trans_status() === FALSE) {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return FALSE;
		} else {
			return TRUE;
		}
	}
}